﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Business.Entities;


namespace Data.Database
{
    public class MateriaAdapter:Adapter
    {
        private static List<Materias> _Materiass;

        private static List<Materias> Materiass
        {
            get
            {
                if (_Materiass == null)
                {
                    _Materiass = new List<Materias>();
                }
                return _Materiass;
            }
        }

        public List<Materias> GetAll() //correcto
        {
            try
            {
                List<Materias> ListaMaterias = new List<Materias>();
                this.OpenConnection();
                SqlCommand cmdMaterias = new SqlCommand("select * from materias m inner join planes p on " +
                    "m.id_plan=p.id_plan order by m.id_plan, m.desc_materia" , sqlConn);
                SqlDataReader drMaterias = cmdMaterias.ExecuteReader();
                while (drMaterias.Read())
                {
                    Materias MateriaActual = new Materias();
                    MateriaActual.ID = (int)drMaterias["id_materia"];
                    MateriaActual.Descripcion = (string)drMaterias["desc_materia"];
                    MateriaActual.HSSemanales = (int)drMaterias["hs_semanales"];
                    MateriaActual.HSTotales = (int)drMaterias["hs_totales"];
                    MateriaActual.IDPlan = (int)drMaterias["id_plan"];
                    MateriaActual.DescripcionPlan = (string)drMaterias["desc_plan"];
                    ListaMaterias.Add(MateriaActual);
                }
                drMaterias.Close();
                return ListaMaterias;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public Materias GetOne(int ID) //correcto
        {
            try
            {
                this.OpenConnection();
                SqlCommand cmdGetMateria = new SqlCommand("select * from materias m where @id=m.id_materia", sqlConn);
                cmdGetMateria.Parameters.Add("@id", typeof(int)).Value = ID;
                SqlDataReader sqlreader = cmdGetMateria.ExecuteReader();
                Materias MatEncontrada = new Materias();
                while (sqlreader.Read())
                {
                    MatEncontrada.ID = (int)sqlreader["id_materia"];
                    MatEncontrada.Descripcion = (string)sqlreader["desc_materia"];
                    MatEncontrada.HSSemanales = (int)sqlreader["hs_semanales"];
                    MatEncontrada.HSTotales = (int)sqlreader["hs_totales"];
                    MatEncontrada.IDPlan = (int)sqlreader["id_plan"];
                }
                sqlreader.Close();
                return MatEncontrada;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public void Delete(Materias mat) //correcto
        {
            try
            {
                Materiass.Remove(mat);
                this.OpenConnection();
                SqlCommand cmdMat = new SqlCommand("delete from materias where id_materia=@id_mat", sqlConn);
                cmdMat.Parameters.Add("@id_mat", typeof(int)).Value = mat.ID;
                cmdMat.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }

        public void Save(Materias materia) //correcto
        {
            try
            {
                this.OpenConnection();
                if (materia.State == Materias.States.New)
                {
                    SqlCommand cmdMat = new SqlCommand("insert into materias(desc_materia, hs_semanales, hs_totales, id_plan) " +
                            "values(@desc_mat, @hs_semanales, @hs_totales, @id_plan)", this.sqlConn);
                    cmdMat.Parameters.Add("@desc_mat", typeof(string)).Value = materia.Descripcion;
                    cmdMat.Parameters.Add("@hs_semanales", typeof(int)).Value = materia.HSSemanales;
                    cmdMat.Parameters.Add("@hs_totales", typeof(int)).Value = materia.HSTotales;
                    cmdMat.Parameters.Add("@id_plan", typeof(int)).Value = materia.IDPlan;
                    cmdMat.ExecuteNonQuery();
                }

                else if (materia.State == Materias.States.Modified)
                {
                    SqlCommand cmdMat = new SqlCommand("update materias set desc_materia=@desc_mat, hs_semanales=@hs_sema, " +
                     "hs_totales=@hs_totales, id_plan=@id_plan where id_materia=@id_materia", this.sqlConn);
                    cmdMat.Parameters.Add("@id_materia", typeof(int)).Value = materia.ID;
                    cmdMat.Parameters.Add("@desc_mat", typeof(string)).Value = materia.Descripcion;
                    cmdMat.Parameters.Add("@hs_sema", typeof(int)).Value = materia.HSSemanales;
                    cmdMat.Parameters.Add("@hs_totales", typeof(int)).Value = materia.HSTotales;
                    cmdMat.Parameters.Add("@id_plan", typeof(int)).Value = materia.IDPlan;
                    cmdMat.ExecuteNonQuery();
                }
                else if (materia.State == Materias.States.Unmodified)
                {

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.CloseConnection();
            }
        }     
    }
}
